package com.yazdankhah.dal;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.sql.Statement;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Set;
import org.apache.log4j.Logger;

/**
 * This class is responsible for all SQL operations. It contains generic and reusable methods.
 *
 * @author Ahmad Yazdankhah
 */
public class SqlUtil {
  
  private static final Logger logger = Logger.getLogger(SqlUtil.class);
  

  public static void freeResource(Statement s) {
    try {
      if (s != null) {
        s.close();
      }

    } catch (SQLException e) {
      logger.error("SQLException due to: " + e.getMessage());
    }

  } //method


  public static void freeResource(ResultSet rs) {
    try {
      if (rs != null) {
        rs.close();
      }

    } catch (SQLException e) {
      logger.error("SQLException due to: " + e.getMessage());
    }

  } //method

  /**
   * Closes the given database connection.
   * @param conn - the given database connection
   * @return true if the operation was successful and false otherwise
   */
  static boolean freeResource(Connection conn) {
    
    boolean res = true;
    
    try {
      if (conn != null) {
        conn.close();
      }

    } catch (SQLException e) {
      logger.error("SQLException during closing the connection due to: " + e.getMessage());
      res = false;
    }
    
    return res;

  } //method


  public static void freeResource(PreparedStatement ps) {
    try {
      if (ps != null) {
        ps.close();
      }

    } catch (SQLException e) {
      logger.error("SQLException due to: " + e.getMessage());
    }

  } //method


  /**
   * Makes n list and put the result set contents as beans into it.
   *
   * @param <E>
   *
   * @pre-condition: the bean MUST have an empty constructor xxx();
   *
   * @param rs  - the sb set
   * @param c   - the class of the bean
   *
   * @return the arrayList
   */
  public static <E> List<E> resultSetToList(ResultSet rs, Class<? extends E> c) {

    try {
      List<E> arr = new ArrayList<>();

      while (rs.next()) {
        E bean = c.newInstance();
        beanMakerEngine(rs, bean);
        arr.add(bean);

      } //while

      return arr;

    } catch (SQLException | InstantiationException | IllegalAccessException e) {
      logger.error("SQLException due to: " + e.getMessage());
      return null;
    }

  } //method


  /**
   * Retrieve the single record and pack it as a bean and return it.
   *
   * @param <E>
   *
   * @pre-condition: the bean MUST have an empty constructor xxx();
   *
   * @param rs  - the sb set
   * @param c   - the class of the bean
   *
   * @return the object
   */
  public static <E> E resultSet2Obj(ResultSet rs, Class<? extends E> c) {

    try {
      //If resultSet is null, it returns an empty bean.
      if (rs == null) {
        return c.newInstance();
      }

      rs.next();
      E bean = c.newInstance();

      //check if the rs has at least one row.
      //If not, it returns an empty bean.
      if (rs.getRow() != 0) {
        beanMakerEngine(rs, bean);
      }

      return bean;

    } catch (SQLException | InstantiationException |
            IllegalAccessException e) {

      logger.error("SQLException due to: " + e.getMessage());
      return null;
    }

  } //method


  /**
   * get the bean fields types.
   *
   * @param bean - the bean object.
   */
  private static <E> List<String> getBeanFieldType(E bean) {

    List<String> arr = new ArrayList<>();
    Field[] f = bean.getClass().getDeclaredFields();

    for (Field field : f) {
      //Ignored serialVersionUID fields because these fields are just for "Serialization".
      if (field.getName().equalsIgnoreCase("serialVersionUID")) continue;
      
      field.setAccessible(true);
      String type = field.getType().toString();
      arr.add(type);

    } //for

    return arr;

  } //method


  /**
   * Get the bean fields values.
   *
   * @param e - the bean object.
   */
  private static <E> List<String> getBeanFieldValue(E bean) {

    List<String> arr = new ArrayList<>();
    Field[] f = bean.getClass().getDeclaredFields();

    for (Field field : f) {
      //Ignored serialVersionUID fields because these fields are just for "Serialization".
      if (field.getName().equalsIgnoreCase("serialVersionUID")) continue;
      
      field.setAccessible(true);
      
      try {
        Object value = field.get(bean);

        if (value == null) {
          arr.add("null");
        } else {
          arr.add(value.toString());
        }
      } catch (IllegalAccessException e) {
        logger.error("IllegalAccessException due to: " + e.getMessage());
      }

    } //for

    return arr;

  } //method


  /**
   * Makes the where clause for delete.
   *
   * @param nameArr  - the array of the columns names.
   * @param valueArr - the values array
   * @param typeArr  - the types array
   *
   * @return the where clause.
   */
  private static String createWHEREClause(List<String> nameArr,
                                          List<String> valueArr, 
                                          List<String> typeArr) {
    
    StringBuilder sb = new StringBuilder();
    String pattern = "AND   %s = %s%n";

    for (int i = 0; i < nameArr.size(); i++) {
      
      // escapes all instance of "'"
      valueArr.set(i, valueArr.get(i).replaceAll("[']", "\'"));

      if (valueArr.get(i).equalsIgnoreCase("null")) continue;
      
//      if (typeArr.get(i).equalsIgnoreCase("int")
//          && Integer.valueOf(valueArr.get(i)) < 0) { //auto-Increment
//        continue;
//      }
      String wrappedValue = wrappValue(valueArr.get(i), typeArr.get(i));
      sb.append(String.format(pattern, nameArr.get(i), wrappedValue));
      
    } //for
    
    logger.debug("The where clause = [" + sb.toString() + "]");

    if (sb.toString().isEmpty()) {
      return null;
    } else {
      return sb.toString().substring(6);
    }

  } //method


  /**
   * Deletes all records of a given table.
   *
   * @param con - the database connection object
   * @param tabName the given table name
   *
   * @return true if the delete operation is successful and false otherwise.
   */
  public static boolean deleteAll(Connection con, String tabName) {

    String stmnt = String.format("DELETE FROM %s", tabName);
    //logger.debug("Delete statement = [ " + stmnt + "]");

    int result = executeUpdate(con, stmnt);
    return result != -1;

  } //method


  /**
   * Deletes a given bean from database table.
   *
   * @param <E> type parameter
   * @param conn - the database connection object
   * @param bean - the given bean
   *
   * @return true if the delete operation is successful and false otherwise.
   */
  public static <E> boolean deleteBean(Connection conn, E bean) {

    String tabName = bean.getClass().getSimpleName();

    List<String> nameArr = getBeanFieldName(bean);
    List<String> typeArr = getBeanFieldType(bean);
    List<String> valueArr = getBeanFieldValue(bean);
    
    String sqlStr = createDELETEStatement(tabName, nameArr, valueArr, typeArr);
    logger.debug("Delete statement = [ " + sqlStr + "]");

    int result = executeUpdate(conn, sqlStr);
    return result != -1;

  } //method


  /** NOTE: PASS OBJECTS AND NOT PRIMITIVE DATA.
   * Checks if an attribute exists.
   *
   * @param con - the database connection object
   * @param sqlStr - the SQL statement string
   * @param parArr - the variable number of parameters
   *
   * @return true if the attribute exists and false otherwise
   */
  public static boolean existanceChaeck(Connection con, String sqlStr, Object... parArr) {

    ResultSet rs = null;

    try (PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      setAttribs(pstmt, parArr);
      rs = pstmt.executeQuery();

      return rs.next();

    } catch (SQLException ex) {
      logger.error("SQLException during existanceChaeck due to: " + ex.getMessage());
      return false;

    } finally {
      freeResource(rs);
    }

  } //method


  /**
   * Get the last inserted ID. Note that it works fine provided that the database connection for the
   * last insertion is not changed. That's why this method need the connection.
   *
   * @param con - the database connection object
   *
   * @return the last inserted ID
   */
  private static int getLastInsertedID(Connection con) {

    String sqlStr = "SELECT last_insert_id()";

    try (
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery(sqlStr);) {

      rs.next();
      int result = rs.getInt(1);
      rs.close();
      return result;

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return -1;
    }

  } //method


  /**
   * Insert a given PK bean and return the last inserted auto increment id. the transaction is not
   * committed.
   *
   * @param <E>
   * @param con
   * @param bean - the given bean object
   *
   * @return the last auto increment id inserted or -1 if unsuccessful
   */
  public static <E> int beanPKInsertEngine(Connection con, E bean) {

    boolean result = insertBean(con, bean);

    if (result) {
      return getLastInsertedID(con);
    } else {
      return -1;
    }

  } //method


//    private static void testbeanInsertEngine2() {
//        
//        Connection con  = getSingleConn();
//        String tabName  = "Component";
//        
//        String compName = "comp2";
//        String compType = "Software";
//        Component c = new Component(compName, compType);
//
//        System.out.println(insertBean(con, c));
//        
//        Table.printTable(con, tabName);
//    } //method
  /**
   * Insert a given FK array list of beans into the appropriate tables.
   *
   * @param <E>
   * @param con
   * @param beanArr - the given bean object array
   *
   * @return true if the insert is successful and false otherwise
   */
  public static <E> boolean beanFKInsertEngine(Connection con, ArrayList<E> beanArr) {

    for (E bean : beanArr) {
      boolean result = insertBean(con, bean);
      if (!result) {
        return false;
      }
    }

    return true;

  } //method


  /**
   * Insert master/detail data into tables in one transaction.
   *
   * @param <E>     - the variable for master bean
   * @param <F>     - the variable for detail bean
   * @param con
   * @param bean    - the given master bean
   * @param beanArr - the given detail array
   *
   * @return true if all inserts are successful and false otherwise
   */
  public static <E, F> boolean transactionPKFKbeanInsertEngine(
          Connection con, E bean, ArrayList<F> beanArr) {

    try {
      con.setAutoCommit(false);
      Savepoint savP = con.setSavepoint();

      //inserting PK
      if (!insertBean(con, bean)) {
        con.rollback(savP);
        con.releaseSavepoint(savP);
        return false;
      }

      //inserting FK
      if (!insertBean(con, beanArr)) {
        con.rollback(savP);
        con.releaseSavepoint(savP);
        return false;
      }

      con.commit();
      con.releaseSavepoint(savP);
      con.setAutoCommit(true);
      return true;

    } //method
    catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return false;
    }
  } //method


  private static void testtransactionPKFKbeanInsertEngine() {
//        String compName = "comp4";
//        String compType = "Software";
//        Component c = new Component(compName, compType);
//        
//        String compName2 = "comp5";
//        String compType2 = "Hardware";
//        Component c2 = new Component(compName2, compType2);
//        
//        String compName1 = "comp4";
//        String compType1 = "Software";
//        Component c = new Component(compName1, compType1);
//        
//        String compName2 = "comp5";
//        int compType2    = 2;
//        Component c2 = new Component(compName2, compType2);
//        
//        ArrayList<Component> beanArr = new ArrayList();
//        beanArr.add(c1);
//        beanArr.add(c2);
//        
//        System.out.println(insertBean(con, beanArr));
//        
//        Table.printTable(con, tabName);
  } //method


  /** NOTE: PASS OBJECTS AND NOT PRIMITIVE DATA.
   * ExecuteUpdate a statement with variable number of objects as the parameters.
   *
   * @param con - the database connection object
   * @param sqlStr - the SQL statement string
   * @param parArr - the variable number of parameters
   *
   * @return - The number of rows affected or -1 if there is a problem
   */
  public static int executeUpdate(Connection con, String sqlStr, Object... parArr) {

    try {
      try (PreparedStatement pstmt = con.prepareStatement(sqlStr)) {
        
        setAttribs(pstmt, parArr);
        //logger.debug("The final SQL before executiron: [" + pstmt.toString());
        return pstmt.executeUpdate();
      }

    } catch (SQLException ex) {
      logger.error("SQLException during executeUpdate due to: " + ex.getMessage());
      return -1;
    }

  } //method


  /**
   * Show master/master objects data
   *
   * @param <M>   - the master part object
   * @param <D>   - the detail part object
   * @param mdArr
   */
  public static <M, D> void showMastreDetailObjectsArr(
          List<MasterDetail<M, D>> mdArr) {

    for (MasterDetail<M, D> md : mdArr) {

      System.out.println("\nMaster = " + md.getMaster());
      List<D> dArr = md.getDetailArr();

      for (D d : dArr) {
        System.out.println("Detail = " + d);
        System.out.println("...........................");
      } //inner-for

      System.out.println("##############################################");

    } //outer-for

  } //method


  /**
   * Search master/detail
   *
   * @throws java.lang.NoSuchFieldException
   * @throws java.lang.IllegalAccessException
   * @throws java.sql.SQLException
   *
   * @precondition: the pk and fk id are integers.
   *
   * @param <M>         - the variable for master bean
   * @param <D>         - the variable for detail bean
   * @param mSQLStr     - SQL string for the master part
   * @param dSQLStr     - SQL string for the detail part
   * @param attribStr   - the attribute value on master table
   * @param mc          - the master class name
   * @param dc          - the detail class name
   * @param fkFieldName - the foreign key field name in master
   *
   * @return an array list of the MasterDetailObject
   */
//  public static <M, D> List<MasterDetail<M, D>> getMasterDetailsByOneAttribute(
//          String mSQLStr, String dSQLStr, String attribStr,
//          Class<? extends M> mc, Class<? extends D> dc,
//          String fkFieldName)
//          throws NoSuchFieldException, IllegalArgumentException,
//                 IllegalAccessException, SQLException {
//
//    List<MasterDetail<M, D>> mdArr = new ArrayList<>();
//
//    try (Connection con = getSingleConn()) {
//      List<M> mArr = getBeans(con, mSQLStr, mc, attribStr);
//
//      for (M mBean : mArr) {
//
//        Field fkField = mBean.getClass().getDeclaredField(fkFieldName);
//        fkField.setAccessible(true);
//        int fkValue = (Integer) fkField.get(mBean);
//        List<D> dArr = getBeans(con, dSQLStr, dc, fkValue);
//        mdArr.add(new MasterDetail<M, D>(mBean, dArr));
//
//      } //for
//    } //try
//
//    return mdArr;
//  } //method
  
  
  /**
   * Search master/detail data from master/detail tables.
   *
   * @throws java.lang.NoSuchFieldException
   * @throws java.lang.IllegalAccessException
   * @throws java.sql.SQLException
   * @precondition: the pk and fk id are integers.
   *
   * @param <M>         - the variable for master bean
   * @param <D>         - the variable for detail bean
   * @param mSQLStr     - SQL string for the master part
   * @param dSQLStr     - SQL string for the detail part
   * @param attribStr   - the attribute value on master table
   * @param mc          - the master class name
   * @param dc          - the detail class name
   * @param fkFieldName - the foreign key field name in master
   *
   * @return an array list of the MasterDetailObject
   */
//  public static <M, D> List<MasterDetail<M, D>> getMasterDetailsByOneAttribute(
//          String mSQLStr, String dSQLStr, int attribStr,
//          Class<? extends M> mc, Class<? extends D> dc,
//          String fkFieldName)
//          throws NoSuchFieldException, IllegalArgumentException,
//                 IllegalAccessException, SQLException {
//
//    List<MasterDetail<M, D>> mdArr = new ArrayList<>();
//
//    try (Connection con = getSingleConn()) {
//      List<M> mArr = getBeans(con, mSQLStr, mc, attribStr);
//
//      for (M mBean : mArr) {
//
//        Field fkField = mBean.getClass().getDeclaredField(fkFieldName);
//        fkField.setAccessible(true);
//        int fkValue = (Integer) fkField.get(mBean);
//        List<D> dArr = (List<D>) getBeans(con, dSQLStr, dc, fkValue);
//        mdArr.add(new MasterDetail<M,D>(mBean, dArr));
//
//      } //for
//    } //try
//
//    return mdArr;
//  } //method
  

  /** NOTE: PASS OBJECTS AND NOT PRIMITIVE DATA.
   * ExecuteQuery a statement with variable number of objects as the parameters.
   *
   * @param con - the database connection object
   * @param sqlStr - the SQL statement string
   * @param parArr - the variable number of parameters
   *
   * @return - The resultSet and null if there is a problem.
   */
  public static ResultSet executeQuery(Connection con, String sqlStr, Object... parArr) {

    try (PreparedStatement pstmnt = con.prepareStatement(sqlStr)) {

      setAttribs(pstmnt, parArr);
      return pstmnt.executeQuery();

    } catch (SQLException ex) {
      logger.error("SQLException during executeUpdate due to: " + ex.getMessage());
      return null;
    }

  } //method


  /**
   * Convert the sb set contents as beans into a hash set.
   *
   * @param <E>
   *
   * @pre-condition: the bean MUST have an empty constructor xxx();
   *
   * @param rs  - the sb set
   * @param c   - the class of the bean
   *
   * @return the HashSet
   */
  public static <E> Set<E> resultSetToHashSet(
          ResultSet rs, Class<? extends E> c) {

    try {
      Set<E> set = new HashSet<>();

      while (rs.next()) {
        E bean = c.newInstance();
        beanMakerEngine(rs, bean);
        set.add(bean);

      } //while

      return set;

    } catch (SQLException | InstantiationException |
            IllegalAccessException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
      return null;
    }

  } //method


  /**
   * Read the first column of sb set and put them in an array list.
   *
   * @pre-condition: the first column MUST be integer;
   * @param rs - the sb set
   * @return the arrayList
   */
  public static List<Integer> resultSetFirstColToIntArr(ResultSet rs) {

    List<Integer> arr = new ArrayList<>();

    try {
      while (rs.next()) {
        arr.add(rs.getInt(1));
      }

    } catch (SQLException ex) {
      return new ArrayList<>(0);
    }

    return arr;

  } //method


  /**
   * Makes a string of all names separated by comma.
   *
   * @param nameArr  - the array of the columns names.
   * @param valueArr - the values array
   * @param typeArr  - the types array
   *
   * @return the where clause.
   */
  private static String createCommaSeparatedStr(List<String> NameArr, 
                                                List<String> valueArr, 
                                                List<String> typeArr) {

    StringBuilder sb = new StringBuilder();

    for (int i = 0; i < NameArr.size(); i++) {

      if (valueArr.get(i).equalsIgnoreCase("null")) continue;
      
//      if (typeArr.get(i).equals("int")
//            && Integer.valueOf(valueArr.get(i)) < 0) continue; //auto-increment

      sb.append(", ").append(NameArr.get(i));
      
    } //for

    return sb.toString().substring(2);

  } //method


  /**
   * Makes a string of all values of an array separated by comma. 
   *
   * @param valueArr - the values array
   * @param typeArr  - the types array
   *
   * @return - the string of the array values.
   */
  private static String createCommaSeparatedStrWrapped(List<String> valueArr, 
                                                       List<String> typeArr)
  throws ParseException {

    StringBuilder sb = new StringBuilder();
    String pattern = ", %s";

    for (int i = 0; i < valueArr.size(); i++) {

      // escapes all instance of "'"
      valueArr.set(i, valueArr.get(i).replaceAll("[']", "''"));

      if (valueArr.get(i).equalsIgnoreCase("null")) continue;
      
//      if (typeArr.get(i).equalsIgnoreCase("int")
//          && Integer.valueOf(valueArr.get(i)) < 0) continue; //auto-increment
      
      String wrappedValue = wrappValue(valueArr.get(i), typeArr.get(i));
      sb.append(String.format(pattern, wrappedValue));

    } //for

    return sb.toString().substring(2);

  } //method


  /**
   * create INSERT statement
   *
   * @param tabName - the table name.
   * @param conName - the array of the columns names.
   * @param values  - the values array
   *
   * @return the INSERT statement.
   */
  private static String createINSERTStatement(String tabName, List<String> nameArr,
                                              List<String> valueArr, List<String> typeArr) {
    
    String pattern = "INSERT INTO %s (%s) \nVALUES (%s);";
    
    try {
      String colStr = createCommaSeparatedStr(nameArr, valueArr, typeArr);
      String valStr = createCommaSeparatedStrWrapped(valueArr, typeArr);

      String sqlStr = String.format(pattern, tabName, colStr, valStr);
      //logger.debug("Insert sqlStr = [\n" + sqlStr + "]");
      
      return sqlStr;
    } catch (ParseException ex) {
      logger.error("SQL exception due to: " + ex.getMessage());
      return null;
    }

  } //method


  private static void testcreateINSERTStatement() {
    String tabName = "test";
    ArrayList<String> nameArr = new ArrayList<>();
    nameArr.add("a");
    nameArr.add("b");
    nameArr.add("c");

    ArrayList<String> valueArr = new ArrayList<>();
    valueArr.add("25");
    valueArr.add("ahmad");
    valueArr.add("-34");

    ArrayList<String> typeArr = new ArrayList<>();
    typeArr.add("int");
    typeArr.add("String");
    typeArr.add("timestamp");

    System.out.println(
            createINSERTStatement(tabName, nameArr, valueArr, typeArr));

  } //method


  /**
   * Inserts a given bean into the appropriate tables.
   *
   * @param <E> the database object bean
   * @param conn - the database connection object
   * @param bean - the given bean 
   *
   * @return true if the insert operation is successful and false otherwise
   */
  public static <E> boolean insertBean(Connection conn, E bean) {

    String tabName = bean.getClass().getSimpleName();

    List<String> nameArr = getBeanFieldName(bean);
    List<String> typeArr = getBeanFieldType(bean);
    List<String> valueArr = getBeanFieldValue(bean);

    String sqlStr = createINSERTStatement(tabName, nameArr, valueArr, typeArr);
    logger.debug("Insert statement = [\n" + sqlStr + "]");


    int result = executeUpdate(conn, sqlStr);
    return result > 0;

  } //method


  private static void testbeanInsertEngine() {

//        Entry entryBean = new Entry(
//            20, "Student", "someone who goes to school", "Daneshjoo", "", "7,8");
//        System.out.println(entryBean);
//
//        System.out.println(insertBean(entryBean));
  } //method


  /**
   * make a statement for UPDATE.
   *
   * @param nameArr  - the array of the columns names.
   * @param valueArr - the values array
   * @param typeArr  - the type array
   * @param pkName   - the primary key name
   * @param tabName  - the table name
   *
   * @return the set clause.
   */
  private static String createUpdateClause(
          List<String> nameArr, List<String> valueArr,
          List<String> typeArr, String pkName, String tabName) {

    StringBuilder sb = new StringBuilder();

    String setStr = null;
    String whereStr = null;

    for (int i = 0; i < valueArr.size(); i++) {
      
      // remove \' from the char types
      valueArr.set(i, valueArr.get(i).replaceAll("[']", "\'"));

      //Test: System.out.println("\nname = " + nameArr.get(i));
      //Test: System.out.println("type = " + typeArr.get(i));
      //Test: System.out.println("value = " + valueArr.get(i));
      //Assumes that the PK is none-string (=int)
      if (nameArr.get(i).equalsIgnoreCase(pkName)) {
        whereStr = String.format("WHERE %s = %s",
                nameArr.get(i), valueArr.get(i));
        continue;

      } //if

      switch (typeArr.get(i)) {
        case "int":
        case "class java.lang.Integer":
          if (!valueArr.get(i).equals("-1")) {
            setStr = String.format(", %s = %s\n", nameArr.get(i), valueArr.get(i));
          }
          break;
        case "double":
        case "float":
        case "class java.lang.Float":
        case "class java.lang.Double":
        case "class java.lang.Boolean":
        case "boolean":
          setStr = String.format(", %s = %s\n",
                  nameArr.get(i), valueArr.get(i));
          break;
        case "class java.lang.String":
          if (valueArr.get(i).isEmpty() || valueArr.get(i).equalsIgnoreCase("null")) {
            setStr = String.format(", %s = null\n", nameArr.get(i));
          } else {
            setStr = String.format(", %s = \'%s\'\n",
                    nameArr.get(i), valueArr.get(i));
          }
          break;

        /* If you put the timestamp null, then it update it by default
         * by the current date-time. That's why it should be updated by
         * the previous value.*/
        case "class java.sql.Timestamp":
          if (valueArr.get(i).equalsIgnoreCase("null")) {
            setStr = String.format(", %s = %s\n",
                    nameArr.get(i), nameArr.get(i));
          } else {
            setStr = String.format(", %s = TIMESTAMP \'%s\'\n",
                    nameArr.get(i), valueArr.get(i));
          }
          break;
      } //switch

      //Test: System.out.println("setStr = " + setStr);
      if (setStr != null) {
        sb.append(setStr);
      }
      setStr = null;

    } //for

    setStr = sb.toString().substring(2);
    return String.format("UPDATE %s \nSET %s %s", tabName, setStr, whereStr);

  } //method


  private static void testcreateUpdateClause() {

    ArrayList<String> nameArr = new ArrayList<>();
    nameArr.add("a");
    nameArr.add("b");
    nameArr.add("c");

    ArrayList<String> typeArr = new ArrayList<>();
    typeArr.add("int");
    typeArr.add("class java.lang.String");
    typeArr.add("int");

    ArrayList<String> valueArr = new ArrayList<>();
    valueArr.add("25");
    valueArr.add("");
    valueArr.add("-1");

    String tabName = "test";
    String pkName = "a";

    System.out.println(createUpdateClause(
            nameArr, valueArr, typeArr, pkName, tabName));

  } //method


  /**
   * get the bean fields Names.
   *
   * @param e - the bean object.
   */
  private static <E> List<String> getBeanFieldName(E bean) {
    
    List<String> arr = new ArrayList<>();

    Field[] f = bean.getClass().getDeclaredFields();

    for (Field field : f) {
      //Ignored serialVersionUID fields because these fields are just for "Serialization".
      if (field.getName().equalsIgnoreCase("serialVersionUID")) continue;
      
      field.setAccessible(true);
      arr.add(field.getName());
    } //for

    return arr;

  } //method

//    private static void testgetBeanFieldName() {
//        Recipe r = new Recipe("Good Name", "Every Thing Mixed",
//                "Every thing", 1, "image");
//
//        ArrayList<String> arr = getBeanFieldName(r);
//
//        System.out.println(arr.toString());
//
//    } //test method

  /**
   * Get the bean fields Names, create a comma delimited string of them.
   *
   * @param <E>
   * @param bean - the bean object.
   *
   * @return the comma separated string of the fields names
   */
  public static <E> String getBeanFieldsNamesCommaSeparated(E bean) {

    StringBuilder sb = new StringBuilder();
    Field[] f = bean.getClass().getDeclaredFields();

    for (Field field : f) {
      sb.append(field.getName()).append(",");
//            System.out.println("fieldName = " + field.getName());
    }

    return sb.toString().substring(0, sb.toString().length() - 1);

  } //method


  private static void testgetBeanFieldsNamesCommaSeparated() {

//        System.out.println(getBeanFieldsNamesCommaSeparated(new Entry()));
  } //test method


  /** NOTE: PASS OBJECTS AND NOT PRIMITIVE DATA.
   * Counts the number of beans in a table.
   *
   * @param con - the database connection object
   * @param sqlStr - the SQL statement string
   * @param parArr - the variable number of parameters
   *
   * @return the count number
   */
  public static Integer countBeans(Connection con, String sqlStr, Object... parArr) {

    ResultSet rs;

    try (PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      setAttribs(pstmt, parArr);
      rs = pstmt.executeQuery();
      rs.next();

      return rs.getInt(1);

    } catch (SQLException ex) {
      logger.error("SQLException during countBeans due to: " + ex.getMessage());
      return null;

    } 

  } //method


  public static <E> boolean beanUpdateEngine(
          Connection con, E bean, String pkName) {

    String tabName = bean.getClass().getSimpleName();

    List<String> nameArr = getBeanFieldName(bean);
    List<String> typeArr = getBeanFieldType(bean);
    List<String> valueArr = getBeanFieldValue(bean);

    String stmnt = createUpdateClause(nameArr, valueArr, typeArr, pkName, tabName);

    //Test: System.out.println("update Statement: \n" + stmnt);
    int result = executeUpdate(con, stmnt);
    return result != -1;

  } //method


  /** NOTE: PASS OBJECTS AND NOT PRIMITIVE DATA.
   * A generic method to get a bean by variable number of attributes
   *
   * @param <E>
   * @param con
   * @param sqlStr - the SQL string
   * @param c      - the class of the object
   * @param parArr - variable number of attributes
   *
   * @return the bean
   */
  public static <E> E getBean(
          Connection con, String sqlStr, Class<? extends E> c, Object... parArr) {

    ResultSet rs = null;

    try (PreparedStatement pstmt = con.prepareStatement(sqlStr);) {

      setAttribs(pstmt, parArr);
      rs = pstmt.executeQuery();
      return resultSet2Obj(rs, c);

    } catch (SQLException e) {
      System.err.println("SQL exception due to: " + e.getMessage());
      return null;

    } finally {
      freeResource(rs);
    }

  } //method


  private static void testgetBean() {

    String sqlStr = "SELECT * FROM EntryW "
                    + "WHERE entry   LIKE ? "
                    + "AND   meaning LIKE ? "
                    + "AND   trans   LIKE ? "
                    + "AND   example LIKE ? "
                    + "AND   vocabID = ? "
                    + "ORDER BY regDate DESC LIMIT ?, 1";

    int vocabID = 20;
    int recNum = 0;
    String entry = "maim";
    String meaning = "";
    String trans = "";
    String example = "";

//        Entry entryw = Sql.getBean(
//            sqlStr, Entry.class, vocabID, recNum,
//            entry, meaning, trans, example);
//        System.out.println(entryw);
  } //test method


  /** NOTE: PASS OBJECTS AND NOT PRIMITIVE DATA.
   * a generic method to get an array list of beans by one int attribute
   *
   * @param <E>    the type parameter
   * @param con    the connection object to database
   * @param sqlStr - the SQL string
   * @param c      - the class of the database object
   * @param parArr the array of the parameters
   *
   * @return the array list of the the beans
   */
  public static <E> List<E> getBeans(Connection con, String sqlStr,
                                     Class<? extends E> c, Object... parArr) {

    ResultSet rs = null;

    try (PreparedStatement pstmt = con.prepareStatement(sqlStr)) {

      setAttribs(pstmt, parArr);
      //logger.debug("pstmt = [" + pstmt + "]");
      rs = pstmt.executeQuery();

      return resultSetToList(rs, c);

    } catch (SQLException ex) {
      logger.error("SQLException due to: " + ex.getMessage());
      return null;

    } finally {
      freeResource(rs);
    }

  } //method


  /**
   * Creates DELETE statement and return it as a string.
   *
   * @param tabName  - the table name
   * @param nameArr  - the columns names array
   * @param valueArr - the values array
   * @param typeArr  - the types array
   *
   * @return the delete statement as a sting
   */
  private static String createDELETEStatement(String tabName, List<String> nameArr,
                                              List<String> valueArr, List<String> typeArr) {
    
    String whereClauseStr = createWHEREClause(nameArr, valueArr, typeArr);
    String deleteStr = String.format("%nDELETE FROM %s \nWHERE %s", tabName, whereClauseStr);

    return deleteStr;

  } //end of method


  private static void testcreateDELETEStatement() {
    String tabName = "test";
    List<String> nameArr = new ArrayList<>();
    nameArr.add("a");
    nameArr.add("b");
    nameArr.add("c");

    List<String> valueArr = new ArrayList<>();
    valueArr.add("-25");
    valueArr.add("null");
    valueArr.add("34");

    List<String> typeArr = new ArrayList<>();
    typeArr.add("int");
    typeArr.add("String");
    typeArr.add("short");

    System.out.println(
            createDELETEStatement(tabName, nameArr, valueArr, typeArr));

  } //method


  /**
   * Makes a new bean and put the resultSet contents into it.
   *
   * @Pre-condition: the resultSet is on the appropriate record.
   *
   * @param rs - the resultSet
   * @param bean  - the bean object that will return the contents of the resultSet.
   *
   * @return the new bean filled with the resultSet data.
   */
  private static <E> void beanMakerEngine(ResultSet rs, E bean) {

    Field[] f = bean.getClass().getDeclaredFields();
    int index = 1;

    for (Field field : f) {
      
      //this field is ignored because its purpose in the bean is for Serialization.
      if (field.getName().equalsIgnoreCase("serialVersionUID")) continue;

      field.setAccessible(true);
      try {
        String type = field.getType().toString();
        //System.out.println("Type = " + type);

        // private fields has modifer = 2
        if (field.getModifiers() != 2) {
          continue;
        }

        switch (type) {
          case "int":
          case "class java.lang.Integer":
            field.set(bean, new Integer(rs.getInt(index)));
            break;
          case "long":
          case "class java.lang.Long":
            //System.out.println("I am in Long = " + rs.getLong(index));
            field.set(bean, new Long(rs.getLong(index)));
            break;
          case "short":
          case "class java.lang.Short":
            //System.out.println("I am in short = " + rs.getShort(index));
            field.set(bean, new Short(rs.getShort(index)));
            break;
          case "double":
          case "class java.lang.Double":
            field.set(bean, new Double(rs.getDouble(index)));
            break;
          case "float":
          case "class java.lang.Float":
            field.set(bean, new Float(rs.getFloat(index)));
            break;
          case "boolean":
          case "class java.lang.Boolean":
            field.set(bean, rs.getBoolean(index));
            break;
          case "class java.lang.String":
            field.set(bean, rs.getString(index));
            break;
          case "class java.sql.Timestamp":
            field.set(bean, rs.getTimestamp(index));
            break;
        } //switch
        
        //System.out.println("Field = [" + field.toString() + "]");

        index++;

      } catch (IllegalAccessException | SQLException ex) {
        System.out.println(ex.getMessage());
      }

    } //for

  } //method


  /**NOTE: To prevent any types of confusion, pass OBJECTS in the varArgs.
   * 
   * A generic method to set PreparedStatment object attributes. 
   *
   * @param pstmt  - the PreparedStatment object
   * @param parArr - the array of parameters of any type (Integer, String)
   */
  public static void setAttribs(PreparedStatement pstmt, Object... parArr) {

    int index = 1;

    try {
      for (Object o : parArr) {
        
        if (o instanceof Integer) {
          pstmt.setInt(index, Integer.valueOf(o.toString()));
          
        } else if (o instanceof String) {
          pstmt.setString(index, o.toString());
          
        } else if (o instanceof Boolean) {
          pstmt.setBoolean(index, Boolean.valueOf(o.toString()));
          
        } else if (o instanceof Double) {
          pstmt.setDouble(index, Double.valueOf(o.toString()));
          
        } else if (o instanceof Float) {
          pstmt.setFloat(index, Float.valueOf(o.toString()));
          
        } else if (o instanceof Long) {
          pstmt.setLong(index, Long.valueOf(o.toString()));
          
        } else if (o instanceof Short) {
          pstmt.setLong(index, Short.valueOf(o.toString()));
          
        } else if (o instanceof BigInteger) {
          pstmt.setBigDecimal(index, new BigDecimal(o.toString())); 
        } 

        index++;
      } //for

    } catch (SQLException ex) {
      System.err.println("SQL exception due to: " + ex.getMessage());
    }

  } //method


  /**
   * Looks at the type and wraps the value inside single quotes accordingly.
   *
   * @param valueArr - the value
   * @param typeArr  - the type
   *
   * @return the wrapped or the same value based on the type
   */
  private static String wrappValue(String value, String type) {
    
    String tempStr = "null";
    
    switch (type) {

      case "int":
      case "double":
      case "short":
      case "long":
      case "float":
      case "boolean":
      case "class java.lang.Integer":
      case "class java.lang.Short":
      case "class java.lang.Long":
      case "class java.lang.Float":
      case "class java.lang.Double":
      case "class java.lang.Boolean":
        tempStr = value;
        break;

      case "class java.lang.String":
        tempStr = "\'" + value + "\'";
        break;

    } //switch
    
    return tempStr;
      
  } //method


  /**
   */
//........................ M A I N   M E T H O D ............................//
  /**
   * This main method is just for testing this class.
   *
   * @param args the arguments
   */
  public static void main(String... args) {

    //testcreateDELETEStatement();
    Short a = 3;
    Object b = (short) 5;
    if (b instanceof Short)
      System.out.println("I am Integer!");

  } //main method

} //class
